﻿IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[esf_sso].[VUser]'))
	DROP VIEW [esf_sso].[VUser]
GO

-- SELECT * FROM [esf_sso].[VUser]

CREATE VIEW [esf_sso].[VUser] AS

	SELECT
		U.Alias							Usr_Alias
		,U.IsEnabled					Usr_IsEnabled
		,U.CreationDateTime				Usr_CreationDateTime
		,U.LastUpdateDateTime			Usr_LastUpdateDateTime		
		,U.Password						Usr_Password
		,U.SecurityQuestionAnswer		Usr_SecurityQuestionAnswer
	
		,VP.[ID]						Usr_Per_ID
		,VP.[FirstName]					Usr_Per_FirstName
		,VP.[SecondName]				Usr_Per_SecondName
		,VP.[SurName]					Usr_Per_SurName
		,VP.[LastName]					Usr_Per_LastName
		,VP.[BirthDate]					Usr_Per_BirthDate
		,VP.[IdentificationNumber]		Usr_Per_IdentificationNumber
		,VP.[Email]						Usr_Per_Email
		,VP.[Profession]				Usr_Per_Profession
		,VP.[Position]					Usr_Per_Position
		,VP.cal_FullName				Usr_Per_cal_FullName

		,VP.MainLoc_CityName			Usr_Per_MainLoc_CityName
		,VP.MainLoc_PhoneNumber			Usr_Per_MainLoc_PhoneNumber
		,VP.MainLoc_CellPhoneNumber		Usr_Per_MainLoc_CellPhoneNumber
		,VP.MainLoc_Address				Usr_Per_MainLoc_Address
		
		,VP.Gen_ID						Usr_Per_Gen_ID
		,VP.Gen_Name					Usr_Per_Gen_Name
		
		,VP.IT_ID						Usr_Per_IT_ID
		,VP.IT_Code						Usr_Per_IT_Code
		,VP.IT_Name						Usr_Per_IT_Name
		
		,VP.Ent_ID						Usr_Per_Ent_ID
		,VP.Ent_Name					Usr_Per_Ent_Name
		
		,VSQ.ID							Usr_Sqa_ID
		,VSQ.Name						Usr_Sqa_Name
		
		,UC.Alias						Usr_Creation_Alias
		,ULU.Alias						Usr_LastUpdate_Alias

		,VCI.ID							Usr_extEnt_ID
		,VCI.Code						Usr_extEnt_Code
		,VCI.Name						Usr_extEnt_Name
	FROM 
		[esf_sso].VPerson VP
	INNER JOIN
		[esf_sso].[User] U ON U.PersonID = VP.ID
	INNER JOIN
		esf_sso.VSecurityQuestion VSQ ON VSQ.ID = U.SecurityQuestionID
	LEFT JOIN
		[esf_sso].[User] UC ON UC.Alias = U.[UserCreationAlias] 
	LEFT JOIN
		[esf_sso].[User] ULU ON ULU.Alias = U.[UserLastUpdateAlias]
	-- A la medida por cliente: CSJ - BELM - SIDN
	LEFT JOIN 
		(SELECT ID, Code, Name FROM esf_sso.VBasicReference WHERE BasicReferenceTypeID = 53) VCI ON 
			VCI.ID = 
			(SELECT TOP 1 UBR.BasicReferenceID FROM esf_sso.User_BasicReference UBR 
			INNER JOIN esf_sso.BasicReference BR ON 
				BR.ID = UBR.BasicReferenceID 
				AND BR.BasicReferenceTypeID = 53
				AND UBR.UserPersonID = U.PersonID) 